﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data; // 用来读取配置文件
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.Linq;

namespace ADO数据库编程
{
    class Program
    {
        
        
        static void Main(string[] args)
        {
            #region DapperDemo
            //DapperDemo dapperDemo = new DapperDemo();

            //dapperDemo.DeletePerson(11);


            //Person person = new Person()
            //{
            //    ID = 11,
            //    Name = "高启胜",
            //    Age = 35,
            //    Addr = "广东",
            //    Sex = "男"
            //};

            //dapperDemo.AddPerson(person);

            //dapperDemo.UpdatePerson(person);

            //IEnumerable<Person> persons = dapperDemo.SelectUser();
            //foreach (var p in persons)
            //{
            //    Console.WriteLine(p);
            //}

            // Person person = dapperDemo.GetPersonById(1L);
            // Console.WriteLine(person);

            #endregion


            #region Linq

            DapperDemo dapperDemo = new DapperDemo();
            IEnumerable<Person> plist = dapperDemo.SelectUser();

            // 使用LINQ查询Id大于5的记录，并且根据年龄进行升序排序
            var query = from person in plist
                        where person.ID > 5
                        orderby person.Age ascending
                        select person;

            // 执行查询并输出结果  
            foreach (var person in query)
            {
                Console.WriteLine($"ID: {person.ID}, Name: {person.Name},Age:{person.Age},Sex:{person.Sex},Addr:{person.Addr}");
            }

            Console.WriteLine("\r\n =============== 分割线 ====================== \r\n"); 

            // 查询id大于5的记录
            var query2 = plist.Where(person => person.ID > 5).OrderBy(e=>e.Age); // order by age asc 升序
            foreach (var person in query2)
            {
                Console.WriteLine($"ID: {person.ID}, Name: {person.Name},Age:{person.Age},Sex:{person.Sex},Addr:{person.Addr}");
            }

            Console.WriteLine("\r\n =============== 分割线 ====================== \r\n");

            var query3 = plist.Where(person => person.ID > 5).OrderByDescending(e => e.Age); // order by age desc 降序
            foreach (var person in query3)
            {
                Console.WriteLine($"ID: {person.ID}, Name: {person.Name},Age:{person.Age},Sex:{person.Sex},Addr:{person.Addr}");
            }

            Console.WriteLine("\r\n =============== 分割线 ====================== \r\n");

            var query4 = from person in plist
                         select new { id = person.ID, name = person.Name, age = person.Age + 5 , addr = person.Addr, sex = person.Sex };
            foreach (var person in query4)
            {
                Console.WriteLine($"ID: {person.id}, Name: {person.name},Age:{person.age},Sex:{person.sex},Addr:{person.addr}");
            }

            Console.WriteLine("\r\n =============== 分割线 ====================== \r\n");

            var query5 = from person in plist
                         where IsEven(person.ID)
                         select new { id = person.ID, name = person.Name, age = person.Age + 5, addr = person.Addr, sex = person.Sex };
            foreach (var person in query5)
            {
                Console.WriteLine($"ID: {person.id}, Name: {person.name},Age:{person.age},Sex:{person.sex},Addr:{person.addr}");
            }

            Console.WriteLine("\r\n =============== 分割线 ====================== \r\n");

            var query6 = from person in plist
                         let n = person.ID % 2 // 临时的存储计算的结果
                         where n == 1 // 这里可以对计算的结果进行比较
                         select new { 
                             id = person.ID, 
                             name = person.Name, 
                             age = person.Age + 5, 
                             addr = person.Addr, 
                             sex = person.Sex 
                         };
            foreach (var person in query6)
            {
                Console.WriteLine($"ID: {person.id}, Name: {person.name},Age:{person.age},Sex:{person.sex},Addr:{person.addr}");
            }

            Console.WriteLine("\r\n =============== 分割线 ====================== \r\n");

            var query7 = from person in plist
                         group person by person.Addr into a
                         select a;
                      
            foreach (IGrouping<string,Person> group in query7)
            {
                Console.WriteLine($"key: {group.Key}");
                foreach(Person person in group)
                {
                    Console.WriteLine($"ID: {person.ID}, Name: {person.Name},Age:{person.Age},Sex:{person.Sex},Addr:{person.Addr}");
                }
            }

            Console.WriteLine("\r\n =============== 分割线 ====================== \r\n");

            var query8 = from person in plist
                         group person by person.Addr into g
                         from p in g
                         select new { k = g.Key,v = p};
            foreach (var p in query8)
            {
                Console.WriteLine($"{p}");
            }

            // 根据id降序排列，然后取第一条记录
            // var sortedPerson = plist.OrderByDescending(p => p.ID).FirstOrDefault();
            // Console.WriteLine($"ID: {sortedPerson.ID}, Name: {sortedPerson.Name}");
            // sortedPerson = plist.OrderByDescending(p => p.ID).FirstOrDefault(p => p.ID > 0);
            // Console.WriteLine($"ID: {sortedPerson.ID}, Name: {sortedPerson.Name}");

            // int count = plist.Count(person => person.ID > 5);
            // Console.WriteLine($"Number of people with ID greater than 5: {count}");

            // count = plist.Where(person => person.ID > 5).Count();
            // Console.WriteLine($"Number of people with ID greater than 5: {count}");

            // long sum = plist.Where(person => person.ID > 5).Sum(p=>p.ID);
            // Console.WriteLine($"Number of people with ID greater than 5: {sum}");

            // var p = from pe in plist
            //        where pe.ID > 5
            //        select pe.ID;

            //foreach(var a in p)
            //{
            //    Console.WriteLine(a);
            //}


            //var p = from pe in plist
            //        where pe.ID > 5
            //        select pe;

            //foreach (var a in p)
            //{
            //    Console.WriteLine(a);
            //} 


            //var p = from pe in plist
            //        where pe.ID > 5
            //        select new { Name = pe.Name, Sex = pe.Sex };

            //foreach (var a in p)
            //{
            //    Console.WriteLine(a);
            //}




            #endregion

            #region SqlCommandDemo

            //SqlCommandDemo demo = new SqlCommandDemo();
            //demo.TestAdd();
            ////demo.TestUpdate();
            ////demo.TestDelete();
            //demo.TestSelect();

            #endregion


            #region SqlCommandBuilderDemo

            //SqlCommandBuilderDemo demo = new SqlCommandBuilderDemo();
            //demo.TestAdd();
            //demo.TestUpdate();
            //demo.TestDelete();

            #endregion


            #region SqlDataAdapterDemo
            //SqlDataAdapterDemo sqlDataAdapterDemo = new SqlDataAdapterDemo();
            //sqlDataAdapterDemo.testSelect();
            //sqlDataAdapterDemo.testInsert();
            //sqlDataAdapterDemo.testUpdate();
            //sqlDataAdapterDemo.testDelete();
            #endregion


            #region SqlConnection

            // SqlConnection sqlConnection = getSqlServerConn(connStr);
            // System.Data.ConnectionState state = sqlConnection.State;
            // Console.WriteLine("连接对象：{0}",state); // 这里是Closed的状态


            // Console.WriteLine(sqlConnection.State);

            // testCommand1();
            // testCommand2();
            // updatePerson();

            #endregion



            Console.ReadKey();
        }


        static bool IsEven(long i)
        {
            return i % 2 == 0 ;
        }



        static void testConn()
        {
            // 获取连接字符串
            String connStr = getConnStr("connSqlServer");
            Console.Write("连接字符串：{0}", connStr);

            // 数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);

            if (conn.State == System.Data.ConnectionState.Closed)
            {
                using (conn)
                {
                    System.Data.ConnectionState state = conn.State;
                    Console.WriteLine("连接对象：{0}", state); // 连接对象默认是关闭状态

                    conn.Open();// 需要手动的开启
                    Console.WriteLine("连接对象：{0}", conn.State);
                }// 大括号执行完后，连接对象自动的关闭了
                Console.WriteLine("连接对象：{0}", conn.State); // 这里是Closed状态
            }
        }

        /// <summary>
        /// 获取连接sqlserver的连接对象
        /// </summary>
        /// <param name="connStr">连接字符串</param>
        /// <returns>连接对象</returns>
        public static SqlConnection getSqlServerConn(String connStr)
        {
            SqlConnection conn = null;
            using (conn = new SqlConnection(connStr))
            {
                conn.Open();

                String sql = "select count(*) from Person ";

                SqlCommand command = new SqlCommand(sql, conn);
                // int rows = command.ExecuteNonQuery();

                // 执行sql命令，返回结果的第一个记录
                object result = command.ExecuteScalar(); 

                Console.WriteLine(conn.State);
                Console.WriteLine(result);
            }
            return conn;

        }

        public static SqlConnection createConn()
        {
            string connStr = getConnStr("connSqlServer");
            SqlConnection conn = new SqlConnection(connStr);
            return conn;
        }


        public static String getConnStr(String key)
        {
            String connectionString = null;

            // 方法一：从配置文件中获取
            if (key == null) key = "connSqlServer";
            connectionString = ConfigurationManager.ConnectionStrings[key].ConnectionString;
            Console.WriteLine(connectionString);


            // 方法二：使用SqlConnectionStringBuilder构造 
            // Data Source=.;Initial Catalog=School;User Id=sa;Password=123456;
            //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            //builder.DataSource = "127.0.0.1";
            //builder.InitialCatalog = "School";
            //builder.UserID = "sa";
            //builder.Password = "123456";

            //connectionString = builder.ConnectionString;

            return connectionString;
        }

        public static MySqlConnection getMySqlConnection(String connStr)
        {
            MySqlConnection conn = null;
            using (conn = new MySqlConnection(connStr))
            {
                conn.Open();
            }
            return conn;
        }


        /// <summary>
        /// 测试命令对象的执行方式，只返回结果的第一个
        /// </summary>
        public static void testCommand1()
        {
            SqlConnection conn = null;
            using (conn = createConn())
            {
                conn.Open();

                String sql = "select count(*) from Person ";

                SqlCommand command = new SqlCommand(sql, conn);
                //int rows = command.ExecuteNonQuery();

                // 执行sql命令，返回结果的第一个记录
                object result = command.ExecuteScalar();

                Console.WriteLine(conn.State);
                Console.WriteLine(result);
            }
            Console.WriteLine(conn.State);
        }

        /// <summary>
        /// 测试命令对象的执行方式，返回列表数据
        /// </summary>
        public static void testCommand2()
        {
            SqlConnection conn = null;
            using (conn = createConn())
            {
                conn.Open();

                String sql = "select * from Person ";

                SqlCommand command = new SqlCommand(sql, conn);

                
                SqlDataReader sqlDataReader = command.ExecuteReader();

                // PersonId,LastName,FirstName,HireDate,EnroollmentDate
                while (sqlDataReader.Read())
                {
                    object id = sqlDataReader["id"];
                    Console.Write(id);
                    Console.Write(" ");
                    object name = sqlDataReader["name"];
                    Console.Write(name);
                    Console.Write(" ");
                    object age = sqlDataReader["age"];
                    Console.Write(age);
                    Console.Write(" ");
                    object sex = sqlDataReader["sex"];
                    Console.Write(sex);
                    Console.Write(" ");
                    object addr = sqlDataReader["addr"];
                    Console.Write(addr);
                    Console.Write(" ");

                    Console.WriteLine("\r\n");
                }
                

                Console.WriteLine(conn.State);
            }
            Console.WriteLine(conn.State);
        }

        /// <summary>
        /// 测试命令对象的执行方式，返回影响记录的行数
        /// </summary>
        public static void updatePerson()
        {
            SqlConnection conn = null;
            using (conn = createConn())
            {
                conn.Open();

                String sql = "update Person set age = 40 where id= 1;";

                SqlCommand command = new SqlCommand(sql, conn);

                int rows = command.ExecuteNonQuery();

                Console.WriteLine(rows);

                Console.WriteLine(conn.State);
            }
            Console.WriteLine(conn.State);
        }
    }


    
}
